import mysql.connector
from mysql.connector import Error
from typing import Optional, List, Dict, Any

class DatabaseManager:
    def __init__(self, host="localhost", user="root", password="2090517353", database="well_data"):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        # self._connect()

    def _connect(self):
        """建立数据库连接"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            print("数据库连接成功")
        except Error as e:
            print(f"数据库连接错误: {e}")

            # 解释:
    def execute_query(self, query: str, params: tuple = None) -> Optional[List[Dict[str, Any]]]:#解释:            """执行查询语句"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute(query, params or ())
            result = cursor.fetchall()
            cursor.close()
            return result
        except Error as e:
            print(f"查询执行错误: {e}")
            return None

    def execute_update(self, query: str, params: tuple = None) -> bool:
        """执行更新语句"""
        if not self.connection or not self.connection.is_connected():
            self._connect()
            if not self.connection:
                return False
                
        try:
            cursor = self.connection.cursor()
            cursor.execute(query, params or ())
            self.connection.commit()
            cursor.close()
            return True
        except Error as e:
            print(f"更新执行错误: {e}")
            return False

    def close(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")

    def create_tables(self):
        """创建必要的数据表"""
        create_tables_queries = [
            """
            CREATE TABLE IF NOT EXISTS well_data (
                id INT AUTO_INCREMENT PRIMARY KEY,
                well_name VARCHAR(100),
                depth FLOAT,
                value FLOAT,
                data_type VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """,
            """
            CREATE TABLE IF NOT EXISTS well_info (
                id INT AUTO_INCREMENT PRIMARY KEY,
                well_name VARCHAR(100) UNIQUE,
                location VARCHAR(200),
                depth FLOAT,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """
        ]
        
        for query in create_tables_queries:
            self.execute_update(query)

    def insert_sample_data(self):
        """插入示例数据"""
        # 插入井信息
        well_info = [
            ("Well-001", "Location A", 1000.0, "测试井1"),
            ("Well-002", "Location B", 1200.0, "测试井2"),
            ("Well-003", "Location C", 1500.0, "测试井3")
        ]
        
        for well in well_info:
            self.execute_update(
                "INSERT IGNORE INTO well_info (well_name, location, depth, description) VALUES (%s, %s, %s, %s)",
                well
            )

        # 为每口井生成测试数据
        for well_name in ["Well-001", "Well-002", "Well-003"]:
            depths = [i * 10.0 for i in range(100)]  # 0-1000米，每10米一个数据点
            for depth in depths:
                # 生成一些随机的测试数据
                value = 100 + depth * 0.1 + (hash(well_name) % 50)  # 使用hash确保不同井的数据有差异
                self.execute_update(
                    "INSERT INTO well_data (well_name, depth, value, data_type) VALUES (%s, %s, %s, %s)",
                    (well_name, depth, value, "测试数据")
                ) 